#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

#if [[ $1 == "" ]];then
#   db=zx_dwb
#else
#   db=$1
#fi

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "CREATE DATABASE IF NOT EXISTS $1zx_dwb;
DROP TABLE IF EXISTS hive.$1zx_dwb.test_dwb_leave_2;
CREATE TABLE hive.$1zx_dwb.test_dwb_leave_2 AS
SELECT
    class_date,
    q.class_id,
    student_id,
    begin_time,
    begin_time_type,
    end_time,
    end_time_type,
    days,
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,
    leave_remark

FROM hive.$1zx_dwd.fact_student_leave_apply q
LEFT JOIN hive.$1zx_dwd.dim_course_table_upload_detail ctu ON q.class_id = ctu.class_id
LEFT JOIN hive.$1zx_dwd.dim_tbh_class_time_table tct ON q.class_id = tct.class_id
WHERE q.audit_state = 1 AND q.cancel_state = 0  AND (content IS NOT NULL )
AND (begin_time BETWEEN use_begin_date AND use_end_date) AND (end_time BETWEEN use_begin_date AND use_end_date);


DROP TABLE IF EXISTS hive.$1zx_dwb.dwb_signin;
CREATE TABLE hive.$1zx_dwb.dwb_signin AS
WITH tmp as (
SELECT
d.class_id,
d.student_id,
d.signin_time,
d.signin_date,
ctu.class_date,
ctu.teacher_id,
ctu.content,   --null 为不需要出勤

if((d.signin_time BETWEEN (date_parse(concat(d.signin_date,' ',tct.morning_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE)
AND (date_parse(concat(d.signin_date,' ',tct.morning_end_time),'%Y-%m-%d %H:%i:%s'))),1,0) as morning,  --粒度时间早上
if((d.signin_time BETWEEN (date_parse(concat(d.signin_date,' ',tct.afternoon_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE)
AND (date_parse(concat(d.signin_date,' ',tct.afternoon_end_time),'%Y-%m-%d %H:%i:%s'))),1,0) as afternoon,
if((d.signin_time BETWEEN (date_parse(concat(d.signin_date,' ',tct.evening_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE)
AND (date_parse(concat(d.signin_date,' ',tct.evening_end_time),'%Y-%m-%d %H:%i:%s'))),1,0) as evening,

date_parse(concat(d.signin_date,' ',tct.morning_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE AS morning_begin_normal,      --上午考勤打卡正常开始范围
date_parse(concat(d.signin_date,' ',tct.morning_begin_time),'%Y-%m-%d %H:%i:%s') + interval '10' MINUTE as morning_end_normal,      --上午考情打卡正常结束范围
date_parse(concat(d.signin_date,' ',tct.morning_end_time),'%Y-%m-%d %H:%i:%s') as morning_end,        --上午考勤结束范围

date_parse(concat(d.signin_date,' ',tct.afternoon_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE AS afternoon_begin_normal,    --下午考勤打卡正常开始范围
date_parse(concat(d.signin_date,' ',tct.afternoon_begin_time),'%Y-%m-%d %H:%i:%s') + interval '10' MINUTE as afternoon_end_normal,    --下午考情打卡正常结束范围
date_parse(concat(d.signin_date,' ',tct.afternoon_end_time),'%Y-%m-%d %H:%i:%s') as afternoon_end,      --下午考勤结束范围

date_parse(concat(d.signin_date,' ',tct.evening_begin_time),'%Y-%m-%d %H:%i:%s') - interval '40' MINUTE AS evening_begin_normal,      --晚上考勤打卡正常开始范围
date_parse(concat(d.signin_date,' ',tct.evening_begin_time),'%Y-%m-%d %H:%i:%s') + interval '10' MINUTE as evening_end_normal,      --晚上考情打卡正常结束范围
date_parse(concat(d.signin_date,' ',tct.evening_end_time),'%Y-%m-%d %H:%i:%s') as evening_end      --晚上考情结束范围

FROM hive.$1zx_dwd.fact_tbh_student_signin_record_new d
LEFT JOIN hive.$1zx_dwd.dim_course_table_upload_detail ctu
ON d.class_id = ctu.class_id AND d.signin_date = ctu.class_date
JOIN hive.$1zx_dwd.dim_tbh_class_time_table tct
ON d.time_table_id =tct.id)
SELECT
    class_id,
    student_id,
    signin_time,
    signin_date,
    class_date,
    content,

    morning,   --早上打卡有效时间
    afternoon,
    evening,

    morning_begin_normal,
    morning_end_normal,
    morning_end,
    afternoon_begin_normal,
    afternoon_end_normal,
    afternoon_end,
    evening_begin_normal,
    evening_end_normal,
    evening_end,
    if((morning = 1 AND (signin_time BETWEEN morning_begin_normal AND morning_end_normal)) ,1,0) as morning_type,  --早上打卡状态 1 正常 0 迟到
    if((afternoon =1 AND (signin_time BETWEEN afternoon_begin_normal AND afternoon_end_normal)),1,0) as afternoon_type,--下午打卡状态
    if((evening = 1 AND (signin_time BETWEEN evening_begin_normal AND evening_end_normal)),1,0) as evening_type,
    min(signin_time) over(PARTITION BY student_id,morning,afternoon,evening)  as min_signin
FROM tmp
WHERE content IS NOT NULL AND  teacher_id IS NOT NULL AND (morning = 1 OR afternoon =1 OR evening =1);
"